<?php
class AdminTicket extends CActiveRecord{

    public static function model($className = __CLASS__) {
        return parent::model ( $className );
    }

    // dat luat cho model
    public function rules(){}         

    // goi den bang can ket noi   
    public function tableName() {                  
        return 'ticket';
    }

    // nhan cac thuong tinh 
    public function attributeLabels(){}
    
    function getRowByTicketPaging($numPage="",$rows_per_page ="",$day){
        $day_now = date('Y-m-d',time());      
        $rows = array();
        if($day==1){
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu, ticket.tien_thuong
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' ORDER BY ticket.id DESC LIMIT ".$numPage.",".$rows_per_page."";    
        } else if($day==0){
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu, ticket.tien_thuong 
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id ORDER BY ticket.id DESC LIMIT ".$numPage.",".$rows_per_page."";
        }
        //echo $sql;
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;       
    }
    
    function getRowByWinner(){
       $day_now = date('Y-m-d',time());
        //$day_now = '2011-05-11';      
        $rows = array();
        $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu,ticket.user_id, ticket.tien_thuong  
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id WHERE tien_thuong > 0 GROUP BY username ORDER BY ticket.id DESC";
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows; 
    }
    
    function getRowByTicketPagingWinner($numPage="",$rows_per_page ="",$day){
        $day_now = date('Y-m-d',time());
        //$day_now = '2011-05-11';      
        $rows = array();
        if($day==1){
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu,ticket.user_id, ticket.tien_thuong  
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' AND tien_thuong > 0 ORDER BY ticket.id DESC LIMIT ".$numPage.",".$rows_per_page."";    
        } else if($day==0){
            $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu,ticket.user_id, ticket.tien_thuong  
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id WHERE tien_thuong > 0 ORDER BY ticket.id DESC LIMIT ".$numPage.",".$rows_per_page."";
        }
        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;       
    }
    
    function countRowByProvinceAndType($province,$type){
        $day_now = date('Y-m-d',time());     
        $rows = array();
        $str =  "";
        $limit =    "";
        if($province != "" && $province != 0){
            $str .= " AND province.id = ".$province;        
        }
        if($kieu_danh != "" && $kieu_danh != 0){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;       
        }
        
        $sql = "SELECT count(ticket.id) as count
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' ".$str;   
        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;        
    }
    
    function getRowByProvinceUserAndType($province,$kieu_danh,$begin,$end){
        $day_now = date('Y-m-d',time());     
        $rows = array();
        $str ="";
        $limit ="";
        if($province != "" && $province != 0){
            $str .= " AND province.id = ".$province;        
        }
        if($kieu_danh != "" && $kieu_danh != 0){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;       
        }
        
        $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu, ticket.tien_thuong
                FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
                left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' ".$str."  ORDER BY ticket.id DESC LIMIT ".$begin.",".$end;   
        //echo $sql;die;
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    function countAllRow($province,$kieu_danh,$begin,$end){
        $rows = array();
        $str ="";
        if($province != "" && $province !=0){
            $str .= " AND province.id = ".$province;
        }
        if($kieu_danh != "" && $kieu_danh != 0){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;
        }
        if($begin != ""){
            $str .= " AND ngay_quay >= '".$begin."'";
        }
        if($end != ""){
            $str .= " AND ngay_quay <= '".$end."'";
        }
        $sql = "SELECT count(ticket.id) as count
        FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
        left join province ON ticket.province_id = province.id  WHERE 1 ".$str." ORDER BY ticket.id DESC ";
        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
      
    }
    
    function getAllRowInDate($province,$kieu_danh,$begin,$end,$numPage,$rows_per_page){

        $rows = array();
        $str ="";
        $limit ="";
        if($province != "" && $province != 0){
            $str .= " AND province.id = ".$province;
        }
        if($begin != ""){
            $str .= " AND ngay_quay >= '".$begin."'";
        }
        if($end != ""){
            $str .= " AND ngay_quay <= '".$end."'";
        }
        if($kieu_danh != 0 && $kieu_danh != ""){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;
        }
        if($rows_per_page != ""){

            $limit .= " LIMIT ".$numPage.",".$rows_per_page;    
        }
        
        $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu, ticket.tien_thuong
        FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
        left join province ON ticket.province_id = province.id  WHERE 1 ".$str." ORDER BY ticket.id DESC ".$limit;
        
 
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }

    function countRowByTienthuong($province, $kieu_danh){
        $day_now = date('Y-m-d',time());      
        $rows = array();
        $str = "";
        if($province != "" && $province != 0){
            $str .= " AND province.id = ".$province;
        }
        if($kieu_danh != "" && $kieu_danh != 0){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;
        }
        $sql = "SELECT count(ticket.id) as count  
        FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
        left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' AND tien_thuong > 0 ".$str;    

        //echo $sql; die;
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryRow();
        return $rows;
    }
    
    function getRowByTienthuong($province, $kieu_danh, $begin, $end){
        $day_now = date('Y-m-d',time());      
        $rows = array();
        $str = "";
        if($province != "" && $province != 0){
            $str .= " AND province.id = ".$province;
        }
        if($kieu_danh != "" && $kieu_danh != 0){
            $str .= " AND ticket.kieu_danh = ".$kieu_danh;
        }
        $sql = "SELECT ticket.id, user_veso.username, DATE_FORMAT(ticket.ngay_quay,'%d/%m/%Y') as ngay_quay_thuong, province.name as province_name, ticket.kieu_danh, ticket.total_xu,ticket.user_id, ticket.tien_thuong  
        FROM ticket left join user_veso ON ticket.user_id = user_veso.id 
        left join province ON ticket.province_id = province.id  WHERE ngay_quay = '".$day_now."' AND tien_thuong > 0 ".$str." ORDER BY ticket.id DESC LIMIT ".$begin.",".$end;    

        //echo $sql; die;
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    function getRowByDateTypeAnhProvince($kieu_danh,$province,$date_from,$date_to){
        $day_now = $date!=""?$date:date('Y-m-d',time());      
        $rows = array();
        $str = "";
        if($province != "" && $province != 0){
            $str .= " AND province_id = ".$province;    
        }
        $sql = "SELECT id, ngay_quay, kieu_danh FROM ticket WHERE ngay_quay >= '".$date_from."' AND ngay_quay <= '".$date_to."' AND kieu_danh = ".$kieu_danh." ".$str; 
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $rows = $command->queryAll();  
        return $rows;
    }
} 